{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SYS_NAME</th>\n",
       "      <th>CWXT_DB:184:C:\\</th>\n",
       "      <th>CWXT_DB:184:D:\\</th>\n",
       "      <th>COLLECTTIME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34270787.33</td>\n",
       "      <td>80262592.65</td>\n",
       "      <td>2014-10-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34328899.02</td>\n",
       "      <td>83200151.65</td>\n",
       "      <td>2014-10-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34327553.50</td>\n",
       "      <td>83208320.00</td>\n",
       "      <td>2014-10-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34288672.21</td>\n",
       "      <td>83099271.65</td>\n",
       "      <td>2014-10-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34190978.41</td>\n",
       "      <td>82765171.65</td>\n",
       "      <td>2014-10-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34187614.43</td>\n",
       "      <td>82522895.00</td>\n",
       "      <td>2014-10-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34285280.22</td>\n",
       "      <td>82590885.00</td>\n",
       "      <td>2014-10-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34290578.41</td>\n",
       "      <td>82368173.30</td>\n",
       "      <td>2014-10-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>33211870.40</td>\n",
       "      <td>82172263.30</td>\n",
       "      <td>2014-10-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>33249253.87</td>\n",
       "      <td>81922685.00</td>\n",
       "      <td>2014-10-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>33253832.53</td>\n",
       "      <td>84844722.95</td>\n",
       "      <td>2014-10-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34328058.03</td>\n",
       "      <td>84769868.90</td>\n",
       "      <td>2014-10-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34328674.80</td>\n",
       "      <td>84558703.40</td>\n",
       "      <td>2014-10-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34234933.61</td>\n",
       "      <td>84207166.80</td>\n",
       "      <td>2014-10-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34022726.41</td>\n",
       "      <td>84042911.90</td>\n",
       "      <td>2014-10-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35016309.47</td>\n",
       "      <td>84129516.15</td>\n",
       "      <td>2014-10-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34981412.82</td>\n",
       "      <td>83877754.85</td>\n",
       "      <td>2014-10-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34828871.38</td>\n",
       "      <td>83887520.40</td>\n",
       "      <td>2014-10-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34832868.75</td>\n",
       "      <td>83538509.75</td>\n",
       "      <td>2014-10-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34843372.75</td>\n",
       "      <td>86483653.00</td>\n",
       "      <td>2014-10-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34847749.42</td>\n",
       "      <td>82496743.30</td>\n",
       "      <td>2014-10-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34829775.93</td>\n",
       "      <td>82300356.65</td>\n",
       "      <td>2014-10-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34824290.49</td>\n",
       "      <td>82130251.65</td>\n",
       "      <td>2014-10-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34827149.93</td>\n",
       "      <td>84844587.65</td>\n",
       "      <td>2014-10-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34820088.89</td>\n",
       "      <td>84845444.65</td>\n",
       "      <td>2014-10-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34776030.47</td>\n",
       "      <td>84684911.05</td>\n",
       "      <td>2014-10-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34667722.51</td>\n",
       "      <td>84500606.35</td>\n",
       "      <td>2014-10-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35705011.09</td>\n",
       "      <td>84145461.25</td>\n",
       "      <td>2014-10-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35594215.37</td>\n",
       "      <td>84172525.10</td>\n",
       "      <td>2014-10-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35411287.07</td>\n",
       "      <td>83957818.70</td>\n",
       "      <td>2014-10-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35389055.02</td>\n",
       "      <td>83684789.75</td>\n",
       "      <td>2014-10-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35385076.36</td>\n",
       "      <td>86485366.95</td>\n",
       "      <td>2014-11-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35462038.02</td>\n",
       "      <td>86454023.45</td>\n",
       "      <td>2014-11-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35486821.33</td>\n",
       "      <td>86127041.70</td>\n",
       "      <td>2014-11-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35471088.77</td>\n",
       "      <td>86161390.40</td>\n",
       "      <td>2014-11-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35547564.55</td>\n",
       "      <td>85938933.90</td>\n",
       "      <td>2014-11-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35606941.11</td>\n",
       "      <td>85645056.50</td>\n",
       "      <td>2014-11-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35546714.13</td>\n",
       "      <td>85272926.05</td>\n",
       "      <td>2014-11-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35510966.73</td>\n",
       "      <td>88110097.75</td>\n",
       "      <td>2014-11-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35491498.51</td>\n",
       "      <td>88128626.65</td>\n",
       "      <td>2014-11-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35601990.55</td>\n",
       "      <td>88075997.75</td>\n",
       "      <td>2014-11-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35687972.60</td>\n",
       "      <td>87753526.65</td>\n",
       "      <td>2014-11-11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   SYS_NAME  CWXT_DB:184:C:\\  CWXT_DB:184:D:\\ COLLECTTIME\n",
       "0    财务管理系统      34270787.33      80262592.65  2014-10-01\n",
       "1    财务管理系统      34328899.02      83200151.65  2014-10-02\n",
       "2    财务管理系统      34327553.50      83208320.00  2014-10-03\n",
       "3    财务管理系统      34288672.21      83099271.65  2014-10-04\n",
       "4    财务管理系统      34190978.41      82765171.65  2014-10-05\n",
       "5    财务管理系统      34187614.43      82522895.00  2014-10-06\n",
       "6    财务管理系统      34285280.22      82590885.00  2014-10-07\n",
       "7    财务管理系统      34290578.41      82368173.30  2014-10-08\n",
       "8    财务管理系统      33211870.40      82172263.30  2014-10-09\n",
       "9    财务管理系统      33249253.87      81922685.00  2014-10-10\n",
       "10   财务管理系统      33253832.53      84844722.95  2014-10-11\n",
       "11   财务管理系统      34328058.03      84769868.90  2014-10-12\n",
       "12   财务管理系统      34328674.80      84558703.40  2014-10-13\n",
       "13   财务管理系统      34234933.61      84207166.80  2014-10-14\n",
       "14   财务管理系统      34022726.41      84042911.90  2014-10-15\n",
       "15   财务管理系统      35016309.47      84129516.15  2014-10-16\n",
       "16   财务管理系统      34981412.82      83877754.85  2014-10-17\n",
       "17   财务管理系统      34828871.38      83887520.40  2014-10-18\n",
       "18   财务管理系统      34832868.75      83538509.75  2014-10-19\n",
       "19   财务管理系统      34843372.75      86483653.00  2014-10-20\n",
       "20   财务管理系统      34847749.42      82496743.30  2014-10-21\n",
       "21   财务管理系统      34829775.93      82300356.65  2014-10-22\n",
       "22   财务管理系统      34824290.49      82130251.65  2014-10-23\n",
       "23   财务管理系统      34827149.93      84844587.65  2014-10-24\n",
       "24   财务管理系统      34820088.89      84845444.65  2014-10-25\n",
       "25   财务管理系统      34776030.47      84684911.05  2014-10-26\n",
       "26   财务管理系统      34667722.51      84500606.35  2014-10-27\n",
       "27   财务管理系统      35705011.09      84145461.25  2014-10-28\n",
       "28   财务管理系统      35594215.37      84172525.10  2014-10-29\n",
       "29   财务管理系统      35411287.07      83957818.70  2014-10-30\n",
       "30   财务管理系统      35389055.02      83684789.75  2014-10-31\n",
       "31   财务管理系统      35385076.36      86485366.95  2014-11-01\n",
       "32   财务管理系统      35462038.02      86454023.45  2014-11-02\n",
       "33   财务管理系统      35486821.33      86127041.70  2014-11-03\n",
       "34   财务管理系统      35471088.77      86161390.40  2014-11-04\n",
       "35   财务管理系统      35547564.55      85938933.90  2014-11-05\n",
       "36   财务管理系统      35606941.11      85645056.50  2014-11-06\n",
       "37   财务管理系统      35546714.13      85272926.05  2014-11-07\n",
       "38   财务管理系统      35510966.73      88110097.75  2014-11-08\n",
       "39   财务管理系统      35491498.51      88128626.65  2014-11-09\n",
       "40   财务管理系统      35601990.55      88075997.75  2014-11-10\n",
       "41   财务管理系统      35687972.60      87753526.65  2014-11-11"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 模型构建 C盘\n",
    "# 详解BIC方式定信息准则　＋　ARIMA \n",
    "import pandas as pd\n",
    "inputfile = 'attrsConstruction.xlsx'\n",
    "\n",
    "data = pd.read_excel(inputfile)\n",
    "df = data.iloc[:len(data)-5]\n",
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(-0.56351181471562739, 0.87901557028092969, 3L, 38L, {'5%': -2.9412623574865142, '1%': -3.6155091011809297, '10%': -2.6091995013850418}, 859.99762204232331)\n",
      "0.879015570281\n",
      "原始序列经过1阶差分后归于平稳，p值为9.57297559233e-07\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\Anaconda2\\lib\\site-packages\\statsmodels\\compat\\pandas.py:56: FutureWarning: The pandas.core.datetools module is deprecated and will be removed in a future version. Please use the pandas.tseries module instead.\n",
      "  from pandas.core import datetools\n",
      "D:\\Anaconda2\\lib\\site-packages\\ipykernel_launcher.py:17: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    }
   ],
   "source": [
    "# 第 * 1 * 步--C盘---------平稳性检测\n",
    "#1)平稳性检测 ：判断是否平稳，若不平稳，对其进行差分处理直至平稳\n",
    "# 方法：采用单位根检验（ADF）的方法或者时序图的方法（见数据探索模块）\n",
    "# 注意：其他平稳性检验方法见steadyCheck.py文件\n",
    "from statsmodels.tsa.stattools import adfuller as ADF\n",
    "diff = 0\n",
    "# 判断D盘数据的平稳性，以及确定几次差分后平稳\n",
    "adf = ADF(df['CWXT_DB:184:C:\\\\'])\n",
    "print adf \n",
    "\n",
    "while adf[1] >= 0.05 : # adf[1]是p值，p值小于0.05认为是平稳的\n",
    "    print adf[1]\n",
    "    diff = diff + 1\n",
    "    adf = ADF(df['CWXT_DB:184:C:\\\\'].diff(diff).dropna())#注意，差分后使用ADF检验时，必须去掉空值\n",
    "    \n",
    "print (u'原始序列经过%s阶差分后归于平稳，p值为%s') % (diff, adf[1])\n",
    "df['CWXT_DB:184:C:\\\\_adf'] = df['CWXT_DB:184:C:\\\\'].diff(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "原始序列为非白噪声序列，对应的p值为：1.06099075081e-08\n",
      "一阶差分序列为白噪声序列，对应的p值为：0.474552255255\n"
     ]
    }
   ],
   "source": [
    "# 第 * 2 * 步--C盘---------白噪声检验\n",
    "# 目的：验证序列中有用信息是否已经被提取完毕，需要进行白噪声检验。若序列是白噪声序列，说明序列中有用信息已经被提取完，只剩随机扰动\n",
    "# 方法：采用LB统计量的方法进行白噪声检验\n",
    "# 若没有通过白噪声检验，则需要进行模型识别，识别其模型属于AR、MA还是ARMA。\n",
    "\n",
    "inputfile2 = 'attrsConstruction.xlsx'\n",
    "data1 = pd.read_excel(inputfile2)\n",
    "data1 = data1.iloc[:len(data1)-5]# 不使用最后五个数据（作为预测参考）\n",
    "\n",
    "# 白噪声检测\n",
    "from statsmodels.stats.diagnostic import acorr_ljungbox\n",
    "\n",
    "[[lb], [p]] = acorr_ljungbox(data1['CWXT_DB:184:C:\\\\'], lags = 1) ## lags是残差延迟个数\n",
    "if p < 0.05:\n",
    "    print (u'原始序列为非白噪声序列，对应的p值为：%s' % p)\n",
    "else:\n",
    "    print (u'原始序列为白噪声序列，对应的p值为：%s' % p)\n",
    "\n",
    "[[lb], [p]] = acorr_ljungbox(data1['CWXT_DB:184:C:\\\\'].diff(1).dropna(), lags = 1)\n",
    "if p < 0.05:\n",
    "    print (u'一阶差分序列为非白噪声序列，对应的p值为：%s' % p)\n",
    "else:\n",
    "    print (u'一阶差分序列为白噪声序列，对应的p值为：%s' % p)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\Anaconda2\\lib\\site-packages\\statsmodels\\tsa\\tsatools.py:628: RuntimeWarning: overflow encountered in exp\n",
      "  newparams = ((1-np.exp(-params))/(1+np.exp(-params))).copy()\n",
      "D:\\Anaconda2\\lib\\site-packages\\statsmodels\\tsa\\tsatools.py:628: RuntimeWarning: invalid value encountered in divide\n",
      "  newparams = ((1-np.exp(-params))/(1+np.exp(-params))).copy()\n",
      "D:\\Anaconda2\\lib\\site-packages\\statsmodels\\tsa\\tsatools.py:629: RuntimeWarning: overflow encountered in exp\n",
      "  tmp = ((1-np.exp(-params))/(1+np.exp(-params))).copy()\n",
      "D:\\Anaconda2\\lib\\site-packages\\statsmodels\\tsa\\tsatools.py:629: RuntimeWarning: invalid value encountered in divide\n",
      "  tmp = ((1-np.exp(-params))/(1+np.exp(-params))).copy()\n",
      "D:\\Anaconda2\\lib\\site-packages\\statsmodels\\tsa\\tsatools.py:584: RuntimeWarning: overflow encountered in exp\n",
      "  newparams = ((1-np.exp(-params))/\n",
      "D:\\Anaconda2\\lib\\site-packages\\statsmodels\\tsa\\tsatools.py:585: RuntimeWarning: overflow encountered in exp\n",
      "  (1+np.exp(-params))).copy()\n",
      "D:\\Anaconda2\\lib\\site-packages\\statsmodels\\tsa\\tsatools.py:585: RuntimeWarning: invalid value encountered in divide\n",
      "  (1+np.exp(-params))).copy()\n",
      "D:\\Anaconda2\\lib\\site-packages\\statsmodels\\tsa\\tsatools.py:586: RuntimeWarning: overflow encountered in exp\n",
      "  tmp = ((1-np.exp(-params))/\n",
      "D:\\Anaconda2\\lib\\site-packages\\statsmodels\\tsa\\tsatools.py:587: RuntimeWarning: overflow encountered in exp\n",
      "  (1+np.exp(-params))).copy()\n",
      "D:\\Anaconda2\\lib\\site-packages\\statsmodels\\tsa\\tsatools.py:587: RuntimeWarning: invalid value encountered in divide\n",
      "  (1+np.exp(-params))).copy()\n",
      "D:\\Anaconda2\\lib\\site-packages\\statsmodels\\base\\model.py:473: HessianInversionWarning: Inverting hessian failed, no bse or cov_params available\n",
      "  'available', HessianInversionWarning)\n",
      "D:\\Anaconda2\\lib\\site-packages\\statsmodels\\base\\model.py:496: ConvergenceWarning: Maximum Likelihood optimization failed to converge. Check mle_retvals\n",
      "  \"Check mle_retvals\", ConvergenceWarning)\n",
      "D:\\Anaconda2\\lib\\site-packages\\statsmodels\\base\\model.py:496: ConvergenceWarning: Maximum Likelihood optimization failed to converge. Check mle_retvals\n",
      "  \"Check mle_retvals\", ConvergenceWarning)\n",
      "D:\\Anaconda2\\lib\\site-packages\\statsmodels\\tsa\\tsatools.py:612: RuntimeWarning: divide by zero encountered in divide\n",
      "  invarcoefs = -np.log((1-params)/(1+params))\n",
      "D:\\Anaconda2\\lib\\site-packages\\statsmodels\\base\\model.py:496: ConvergenceWarning: Maximum Likelihood optimization failed to converge. Check mle_retvals\n",
      "  \"Check mle_retvals\", ConvergenceWarning)\n",
      "D:\\Anaconda2\\lib\\site-packages\\statsmodels\\base\\model.py:496: ConvergenceWarning: Maximum Likelihood optimization failed to converge. Check mle_retvals\n",
      "  \"Check mle_retvals\", ConvergenceWarning)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "             0            1            2            3            4\n",
      "0  1166.398597  1169.307301  1170.991139  1168.427478  1172.077129\n",
      "1  1169.644775          NaN  1170.684719  1171.724685  1175.002346\n",
      "2  1172.246538  1170.593519          NaN  1174.762634  1180.268815\n",
      "3  1170.473345  1173.422439  1177.114533  1180.531660          NaN\n",
      "4  1173.366376  1177.079054  1178.166253          NaN  1181.982648\n",
      "0  0    1166.398597\n",
      "   1    1169.307301\n",
      "   2    1170.991139\n",
      "   3    1168.427478\n",
      "   4    1172.077129\n",
      "1  0    1169.644775\n",
      "   2    1170.684719\n",
      "   3    1171.724685\n",
      "   4    1175.002346\n",
      "2  0    1172.246538\n",
      "   1    1170.593519\n",
      "   3    1174.762634\n",
      "   4    1180.268815\n",
      "3  0    1170.473345\n",
      "   1    1173.422439\n",
      "   2    1177.114533\n",
      "   3    1180.531660\n",
      "4  0    1173.366376\n",
      "   1    1177.079054\n",
      "   2    1178.166253\n",
      "   4    1181.982648\n",
      "dtype: float64\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\Anaconda2\\lib\\site-packages\\statsmodels\\base\\model.py:496: ConvergenceWarning: Maximum Likelihood optimization failed to converge. Check mle_retvals\n",
      "  \"Check mle_retvals\", ConvergenceWarning)\n"
     ]
    }
   ],
   "source": [
    "# 第 * 3 * 步----------模型识别\n",
    "# 方法：采用极大似然比方法进行模型的参数估计，估计各个参数的值。\n",
    "# 然后针对各个不同模型，采用信息准则方法（有三种：BIC/AIC/HQ)对模型进行定阶，确定p,q参数，从而选择最优模型。\n",
    "# 注意，进行此步时，index需要为时间序列类型\n",
    "# 确定最佳p、d、q的值\n",
    "inputfile3 = 'attrsConstruction.xlsx'\n",
    "data2 = pd.read_excel(inputfile3,index_col='COLLECTTIME')\n",
    "xtest_value=data2['CWXT_DB:184:C:\\\\'][-5:]\n",
    "data2 = data2.iloc[:len(data2)-5]# 不使用最后五个数据（作为预测参考） \n",
    "# print data2\n",
    "xdata2 = data2['CWXT_DB:184:C:\\\\']\n",
    "# print xdata2\n",
    "# ARIMA（p,d,q）中,AR是自回归,p为自回归项数；MA为滑动平均,q为滑动平均项数,d为使之成为平稳序列所做的差分次数(阶数)，由前一步骤知d=1\n",
    "# from statsmodels.tsa.arima_model import ARIMA#建立ARIMA（p,d，q）模型\n",
    "from statsmodels.tsa.arima_model import ARIMA #建立ARIMA（p,q）模型\n",
    "\n",
    "# 定阶\n",
    "# 目前选择模型常用如下准则!!!!!\n",
    "# 增加自由参数的数目提高了拟合的优良性，\n",
    "# AIC/BIC/HQ鼓励数据拟合的优良性但是尽量避免出现过度拟合(Overfitting)的情况。所以优先考虑的模型应是AIC/BIC/HQ值最小的那一个\n",
    "#* AIC=-2 ln(L) + 2 k 中文名字：赤池信息量 akaike information criterion (AIC)\n",
    "# * BIC=-2 ln(L) + ln(n)*k 中文名字：贝叶斯信息量 bayesian information criterion (BIC)\n",
    "# * HQ=-2 ln(L) + ln(ln(n))*k hannan-quinn criterion (HQ)\n",
    "\n",
    "# ----------------------------------------------------------\n",
    "pmax = int(len(xdata2)/10) # 一般阶数不超过length/10\n",
    "qmax = int(len(xdata2)/10) # 一般阶数不超过length/10\n",
    "\n",
    "bic_matrix = [] # bic矩阵\n",
    "for p in range(pmax+1):\n",
    "    tmp = []\n",
    "    for q in range(qmax+1):\n",
    "        try:#存在部分为空值，会报错\n",
    "#             print ARIMA(xdata2, (p,1,q)).fit().bic\n",
    "            tmp.append(ARIMA(xdata2, (p,1,q)).fit().bic) #  BIC方式\n",
    "        except:\n",
    "            tmp.append(None)\n",
    "            \n",
    "    bic_matrix.append(tmp)\n",
    "    \n",
    "bic_matrix = pd.DataFrame(bic_matrix) # 从中可以找出最小值\n",
    "print bic_matrix\n",
    "print bic_matrix.stack()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "当前BIC最小的p值与q值分别为：0、0\n",
      "(0, 0)\n",
      "模型ARIMA(0,1,0)符合白噪声检验\n"
     ]
    }
   ],
   "source": [
    "# 第 * 4 * 步--C盘---------模型检验\n",
    "# 确定模型后，需要检验其残差序列是否是白噪声，若不是，说明，残差中还存在有用的信息，需要修改模型或者进一步提取。\n",
    "# 若其残差不是白噪声，重新更换p,q的值，重新确定\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "while 1:\n",
    "    p, q = bic_matrix.stack().idxmin() # 先展平该表格，然后找出最小值的索引位置\n",
    "    print (u'当前BIC最小的p值与q值分别为：%s、%s' % (p,q))\n",
    "    \n",
    "    lagnum = 12 # 残差延迟个数\n",
    "\n",
    "    # 由模型识别可知第一次BIC最小的p值与q值分别为：0、1\n",
    "\n",
    "    arima = ARIMA(xdata2, (p,1,q)).fit() # 建立并训练模型\n",
    "    xdata_pred = arima.predict(typ = 'levels') # 预测\n",
    "    pred_error = (xdata_pred - xdata2).dropna() # 计算残差\n",
    "\n",
    "    # 白噪声检测\n",
    "    from statsmodels.stats.diagnostic import acorr_ljungbox\n",
    "\n",
    "    lbx, px = acorr_ljungbox(pred_error, lags = lagnum)\n",
    "    h = (px < 0.05).sum() # p值小于0.05，认为是非噪声\n",
    "    if h > 0:\n",
    "        print (u'模型ARIMA(%s,1,%s)不符合白噪声检验' % (p,q))\n",
    "        print ('在BIC矩阵中去掉[%s,%s]组合，重新进行计算' % (p,q))\n",
    "        bic_matrix.iloc[p,q] =  np.nan\n",
    "        arimafail = arima\n",
    "        continue\n",
    "    else:\n",
    "        print (p,q)\n",
    "        print (u'模型ARIMA(%s,1,%s)符合白噪声检验' % (p,q))\n",
    "        break\n",
    "        \n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "ename": "IndexError",
     "evalue": "list index out of range",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mIndexError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-6-d5bb57d58634>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0marima\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msummary\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;31m# 当p,q值为0，0时，summary方法报错\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32mD:\\Anaconda2\\lib\\site-packages\\statsmodels\\tsa\\arima_model.pyc\u001b[0m in \u001b[0;36msummary\u001b[1;34m(self, alpha)\u001b[0m\n\u001b[0;32m   1622\u001b[0m         smry.add_table_2cols(self, gleft=top_left, gright=top_right,\n\u001b[0;32m   1623\u001b[0m                              title=title)\n\u001b[1;32m-> 1624\u001b[1;33m         \u001b[0msmry\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0madd_table_params\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0malpha\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0malpha\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0muse_t\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mFalse\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1625\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1626\u001b[0m         \u001b[1;31m# Make the roots table\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mD:\\Anaconda2\\lib\\site-packages\\statsmodels\\iolib\\summary.pyc\u001b[0m in \u001b[0;36madd_table_params\u001b[1;34m(self, res, yname, xname, alpha, use_t)\u001b[0m\n\u001b[0;32m    859\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mres\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mparams\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mndim\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;36m1\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    860\u001b[0m             table = summary_params(res, yname=yname, xname=xname, alpha=alpha,\n\u001b[1;32m--> 861\u001b[1;33m                                    use_t=use_t)\n\u001b[0m\u001b[0;32m    862\u001b[0m         \u001b[1;32melif\u001b[0m \u001b[0mres\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mparams\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mndim\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;36m2\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    863\u001b[0m \u001b[1;31m#            _, table = summary_params_2dflat(res, yname=yname, xname=xname,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mD:\\Anaconda2\\lib\\site-packages\\statsmodels\\iolib\\summary.pyc\u001b[0m in \u001b[0;36msummary_params\u001b[1;34m(results, yname, xname, alpha, use_t, skip_header, title)\u001b[0m\n\u001b[0;32m    479\u001b[0m                                   \u001b[0mparams_stubs\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    480\u001b[0m                                   \u001b[0mtitle\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mtitle\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 481\u001b[1;33m                                   \u001b[0mtxt_fmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mfmt_params\u001b[0m \u001b[1;31m#gen_fmt #fmt_2, #gen_fmt,\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    482\u001b[0m                                   )\n\u001b[0;32m    483\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mD:\\Anaconda2\\lib\\site-packages\\statsmodels\\iolib\\table.pyc\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, data, headers, stubs, title, datatypes, csv_fmt, txt_fmt, ltx_fmt, html_fmt, celltype, rowtype, **fmt_dict)\u001b[0m\n\u001b[0;32m    184\u001b[0m         \"\"\"\n\u001b[0;32m    185\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtitle\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mtitle\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 186\u001b[1;33m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_datatypes\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdatatypes\u001b[0m \u001b[1;32mor\u001b[0m \u001b[0mlrange\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mlen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    187\u001b[0m         \u001b[1;31m# start with default formatting\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    188\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_txt_fmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdefault_txt_fmt\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcopy\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mIndexError\u001b[0m: list index out of range"
     ]
    }
   ],
   "source": [
    "arima.summary() # 当p,q值为0，0时，summary方法报错"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 35722538.09439024,  35757103.58878049,  35791669.08317073,\n",
       "        35826234.57756097,  35860800.07195121])"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "forecast_values, forecasts_standard_error, forecast_confidence_interval = arima.forecast(5)\n",
    "forecast_values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "DatetimeIndex: 5 entries, 2014-11-12 to 2014-11-16\n",
      "Data columns (total 2 columns):\n",
      "实际值    5 non-null float64\n",
      "预测值    5 non-null float64\n",
      "dtypes: float64(2)\n",
      "memory usage: 120.0 bytes\n"
     ]
    }
   ],
   "source": [
    "predictdata = pd.DataFrame(xtest_value)\n",
    "predictdata.insert(1,'CWXT_DB:184:C:\\\\_predict',forecast_values)\n",
    "predictdata.rename(columns={'CWXT_DB:184:C:\\\\':u'实际值','CWXT_DB:184:C:\\_predict':u'预测值'},inplace=True)\n",
    "predictdata.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>实际值</th>\n",
       "      <th>预测值</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>COLLECTTIME</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2014-11-12</th>\n",
       "      <td>35704312.58</td>\n",
       "      <td>35722538.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-13</th>\n",
       "      <td>35704980.73</td>\n",
       "      <td>35757103.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-14</th>\n",
       "      <td>34570385.45</td>\n",
       "      <td>35791669.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-15</th>\n",
       "      <td>34673820.69</td>\n",
       "      <td>35826234.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-16</th>\n",
       "      <td>34793245.31</td>\n",
       "      <td>35860800.07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     实际值          预测值\n",
       "COLLECTTIME                          \n",
       "2014-11-12   35704312.58  35722538.09\n",
       "2014-11-13   35704980.73  35757103.59\n",
       "2014-11-14   34570385.45  35791669.08\n",
       "2014-11-15   34673820.69  35826234.58\n",
       "2014-11-16   34793245.31  35860800.07"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_d = predictdata.applymap(lambda x: '%.2f' % x) # 将表格中各个浮点值都格式化\n",
    "result_d.to_excel('pedictdata_C_BIC_ARIMA.xlsx')\n",
    "result_d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                   实际值        预测值\n",
      "COLLECTTIME                      \n",
      "2014-11-12   35.704313  35.722538\n",
      "2014-11-13   35.704981  35.757104\n",
      "2014-11-14   34.570385  35.791669\n",
      "2014-11-15   34.673821  35.826235\n",
      "2014-11-16   34.793245  35.860800\n",
      "0.70232013\n",
      "0.890203752645\n",
      "0.0202432790493\n",
      "误差阈值为1.5\n",
      "平均绝对误差为：0.7023, \n",
      "均方根误差为：0.8902, \n",
      "平均绝对百分误差为：0.0202\n",
      "误差检验通过！\n"
     ]
    }
   ],
   "source": [
    "# 第 * 5 * 步--D盘---------模型评价\n",
    "# 为了评价时序预测模型效果的好坏，本章采用3个衡量模型预测精度的统计量指标：平均绝对误差、均方根误差、平均绝对百分误差\n",
    "# -*- coding:utf-8 -*-\n",
    "import pandas as pd\n",
    "\n",
    "inputfile4 = 'pedictdata_C_BIC_ARIMA.xlsx'\n",
    "result = pd.read_excel(inputfile4,index_col='COLLECTTIME')\n",
    "result = result.applymap(lambda x: x/10**6)\n",
    "print result\n",
    "\n",
    "# 计算误差\n",
    "abs_ = (result[u'预测值']-result[u'实际值']).abs()\n",
    "mae_ = abs_.mean() # mae平均绝对误差\n",
    "rmas_ = ((abs_**2).mean())**0.5 #rmas均方根误差\n",
    "mape_ = (abs_/result[u'实际值']).mean() #mape平均绝对百分误差\n",
    "# print abs_\n",
    "print mae_\n",
    "print rmas_\n",
    "print mape_\n",
    "errors = 1.5\n",
    "print '误差阈值为%s' % errors\n",
    "if (mae_ < errors) & (rmas_ < errors) & (mape_ < errors):\n",
    "    print (u'平均绝对误差为：%.4f, \\n均方根误差为：%.4f, \\n平均绝对百分误差为：%.4f' % (mae_, rmas_, mape_))\n",
    "    print '误差检验通过！'\n",
    "else:\n",
    "    print '误差检验不通过！'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
